package com.enfry.common.util.excel;

import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Locale;
import java.util.TimeZone;

import jxl.CellType;
import jxl.DateCell;
import jxl.NumberCell;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Row;

import com.enfry.common.util.String.Strings;
import com.enfry.common.util.data.DateTimes;

public class ExcelFileUtil {

	public static boolean isEmptyRow(jxl.Cell[] cells) {
        if (cells == null) {
            return true;
        }
        for (jxl.Cell cell : cells) {
            String val = StringUtils.trimToEmpty(cell.getContents());
            if (!StringUtils.isBlank(val)) {
                return false;
            }
        }
        return true;
    }
	
	public static boolean isEmptyRow(Row row) {
        if (row == null) {
            return true;
        }
        for (org.apache.poi.ss.usermodel.Cell cell : row) {
            String val = StringUtils.trimToEmpty(getCellContent(cell));
            if (!StringUtils.isBlank(val)) {
                return false;
            }
        }


        return true;
    }
	
	@SuppressWarnings("deprecation")
	public static String getCellContent(org.apache.poi.ss.usermodel.Cell cell) {
        StringBuffer buffer = new StringBuffer();
        switch (cell.getCellType()) {
            case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC : // 数字
            	String result = null;
            	if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式  
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//                    System.out.println(cell.getCellStyle().getDataFormat());
//                    if (cell.getCellStyle().getDataFormat() == HSSFDataFormat
//                            .getBuiltinFormat("h:mm")) {
//                        sdf = new SimpleDateFormat("HH:mm");
//                    } else if (cell.getCellStyle().getDataFormat() == HSSFDataFormat
//                            .getBuiltinFormat("m/d/yy h:mm") ||
//                            cell.getCellStyle().getDataFormat() == HSSFDataFormat
//                                    .getBuiltinFormat("yyyy/m/d h:mm:ss")) {
//                        sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//                    } else {// 日期
//                        sdf = new SimpleDateFormat("yyyy-MM-dd");
//                    }
                    Date date = cell.getDateCellValue();
                    result = sdf.format(date);
                } else if (cell.getCellStyle().getDataFormat() == 58) {  
                    // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)  
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");  
                    double value = cell.getNumericCellValue();  
                    Date date = org.apache.poi.ss.usermodel.DateUtil  
                            .getJavaDate(value);  
                    result = sdf.format(date);  
                }else if(cell.getCellStyle().getDataFormat()==49) {
                    DecimalFormat format = new DecimalFormat("0");
                    result=format.format(cell.getNumericCellValue());
                }else {
                    double value = cell.getNumericCellValue();//;
                    BigDecimal bg=new BigDecimal(value+"");
                    result = Strings.toString(bg);
                }
                buffer.append(result);
                break;
            case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN : // 布尔
                buffer.append(cell.getBooleanCellValue());
                break;
            case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA : // 公式
            	 buffer.append(Strings.toString(cell.getNumericCellValue()));
                break;
            case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING : // 字符串
                buffer.append(cell.getStringCellValue());
                break;
            case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK : // 空值
            case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_ERROR : // 故障
            default :
                break;
        }
        return buffer.toString();
    }
	
	public static String getCellContent(jxl.Cell cell){
        StringBuffer buffer = new StringBuffer();
        try{
	        if(cell.getType()==CellType.DATE) {
	        	DateCell dc = (DateCell)cell;
	        	Date jxlDate = new Date(dc.getDate().getTime());	//获取单元格的date类型
	    		if(jxlDate!=null){
	    	        TimeZone gmt = TimeZone.getTimeZone("GMT");
	    			DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss",Locale.getDefault());
	    	        dateFormat.setTimeZone(gmt);
	    	        String str = dateFormat.format(jxlDate);
	    	      
	    	        TimeZone local = TimeZone.getDefault();
	    	        dateFormat.setTimeZone(local);
	    	        Date localDate = dateFormat.parse(str);
	    	        buffer.append(DateTimes.toDateTimeString(localDate, "yyyy-MM-dd HH:mm:ss"));
	    	    }
	        }else if(cell.getType()==CellType.NUMBER 
	        		||cell.getType()==CellType.NUMBER_FORMULA){  
	            NumberCell nc=(NumberCell)cell;  
	            buffer.append(nc.getValue());
	        }else{
	        	buffer.append(cell.getContents());
	        }
        }catch(Exception e){
        	
        }
        return buffer.toString();
    }
}
